package com.data.dao;

import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.springframework.jdbc.core.simple.ParameterizedBeanPropertyRowMapper;
import org.springframework.stereotype.Repository;

import com.base.dao.TemplateDaoImpl;
import com.data.entity.StateMonth;
@Repository("stateMonthDao")
public class StateMonthDaoImpl extends TemplateDaoImpl<StateMonth, Integer>implements StateMonthDao{
	/**
	 * 根据设备id，设备状态，所属日期查询设备状态月记录
	 * @param equId
	 * @param stateType
	 * @param belongDate
	 * @return
	 */
	@Override
	public StateMonth getStateMonthByDate(int equId, String stateType,String belongDate) {
			String  sql="select * from stateMonth where equId=:equId and stateType=:stateType  and  CONVERT(VARCHAR(7),belongDate,120)=:belongDate";
			Map<String, Object> params=new HashMap<String, Object>();
			params.put("equId", equId);
			params.put("stateType", stateType);
			params.put("belongDate", belongDate);
			return jt.queryForObject(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMonth.class));
	}

	/**
	 * 获取时间区间内的所有工作时长
	 * */
	@Override
	public Double getTotalWorkTime(int equId, String startDate, String endDate ,String stateType) {
		StringBuilder sql = new StringBuilder("select COALESCE(SUM(duration),0) from stateMonth where equId=:equId and CONVERT(VARCHAR(7),belongDate,120)>=:startDate and CONVERT(VARCHAR(7),belongDate,120)<=:endDate ");
		Map<String, Object> params=new HashMap<String, Object>();
		if(stateType==null||stateType.equals("")){
			sql.append(" and stateType<>'停机' ");
		}else{
			sql.append(" and stateType=:stateType ");
			params.put("stateType", stateType);
		}
		params.put("equId", equId);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sql.toString(), params, Double.class);
	}

	@Override
	public List<StateMonth> getBySameMonth(int equId, String belongMonth,
			String stateType) {
		String  sql="select * from stateMonth where equId=:equId and SUBSTRING(Convert(varchar(10),belongDate,112),6,2)=:belongMonth and stateType=:stateType ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("belongMonth", belongMonth);
		params.put("stateType", stateType);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMonth.class));
	}

	@Override
	public List<StateMonth> getDistinctType(int equId, String startDate,
			String endDate) {
		String  sql="select distinct(stateType) from stateMonth where equId=:equId and CONVERT(VARCHAR(7),belongDate,120)>=:startDate and CONVERT(VARCHAR(7),belongDate,120)<=:endDate ";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMonth.class));
	
	}

	@Override
	public StateMonth getDurationByTypeAndDate(int equId,
			String startDate, String endDate, String stateType) {
//		System.out.println(equId+"||"+startDate);
		String  sql="select COALESCE(SUM(duration),0) duration ,COALESCE(SUM(relativeComp),0) relativeComp from stateMonth where equId=:equId and stateType=:stateType  and CONVERT(VARCHAR(7),belongDate,120)>=:startDate and CONVERT(VARCHAR(7),belongDate,120)<=:endDate";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("stateType", stateType);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		return jt.queryForObject(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMonth.class));

	}

	@Override
	public List<StateMonth> getStateMonthByStateAndDates(int equId,
			String startDate, String endDate, String stateType) {
		String  sql="select * from stateMonth where equId=:equId and stateType=:stateType and CONVERT(VARCHAR(7),belongDate,120)>=:startDate and CONVERT(VARCHAR(7),belongDate,120)<=:endDate";
		Map<String, Object> params=new HashMap<String, Object>();
		params.put("equId", equId);
		params.put("startDate", startDate);
		params.put("endDate", endDate);
		params.put("stateType", stateType);
		return jt.query(sql, params, ParameterizedBeanPropertyRowMapper.newInstance(StateMonth.class));
	
	}

	
	
}
